package com.qqt;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

public class JdbcPool {

    private List<Connection> pool = new LinkedList<Connection>();

    private static class SingletonInstance {
        private static final JdbcPool INSTANCE = new JdbcPool();
    }

    public static JdbcPool getInstance() {
        return SingletonInstance.INSTANCE;
    }

    private JdbcPool() {
        try {
            Class.forName("org.hsqldb.jdbcDriver");
            for (int i = 0; i < 10; i++) {
                pool.add(DriverManager.getConnection("jdbc:hsqldb:mem:spring", "root", "123456"));
            }
            createTable();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public void createTable() {
        Connection connection = null;
        try {
            connection = getConnection();
            Statement stmt = connection.createStatement();
            String sql = "CREATE TABLE USER( " +
                    "id INTEGER PRIMARY KEY, " +
                    "name VARCHAR(32) , " +
                    "phone VARCHAR(11) , " +
                    "age INTEGER )";
            System.out.println("Create Table Sql:" + sql);
            stmt.executeUpdate(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            release(connection);
        }
    }

    public Connection getConnection() {
        return pool.remove(0);
    }

    public void release(Connection connection) {
        if (connection != null) {
            pool.add(connection);
        }
    }
}
